package util;

import pojoCla.News;
import pojoCla.NewsChnl;
import pojoCla.SysUser;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class NewsUtil {
    private DButil db;
    private ChnlUtil cu;
    //呵呵哒
    public boolean addNews(String addNewsCenter, String keyword, String addNewsChnlName, int addNewsTop, String addNewsText, String addUserAcct) {
        db = new DButil();
        cu = new ChnlUtil();
        String chnlCode = cu.findChnlName(addNewsChnlName);
        Timestamp tt = new Timestamp(new Date().getTime());
        String sql = "insert into news(title,chnl_code,detail,keyword,top,state,optor,crtime,uptime) values(?,?,?,?,?,?,?,?,?)";
        Object[] obj = {addNewsCenter, chnlCode, addNewsText, keyword, addNewsTop, 1, addUserAcct, tt, tt};
        try {
            db.update(sql, obj);
            db.submitUpdate();
        } catch (Exception e) {
            try {
                db.getConn().rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            db.closeConn();
        }
        return true;
    }

    public Integer getNewsSumPage(int pageSize) {
        db = new DButil();
        Integer sum = null;

        String sql = "select count(*) from news";
        try {
            ResultSet query = db.query(sql, new Object[]{});
            if (query.next()) {
                sum = query.getInt(1);
            }
            sum = sum % pageSize == 0 ? sum / pageSize : sum / pageSize + 1;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeConn();
        }
        return sum;
    }
    public Integer getNewsSumPageByUser(List<NewsChnl> list,int pageSize){
        db = new DButil();
        Integer sum = null;
        Object[] code = new String[list.size()];
        StringBuilder sql = new StringBuilder("select count(*) from news where chnl_code in (");
        try {
            for (int i = 0; i < list.size(); i++) {
                code[i] = list.get(i).getCode();
                sql.append("?,");
            }
            sql.replace(sql.length()-1,sql.length(),")");
            ResultSet query = db.query(sql.toString(), code);
            if(query.next()){
                sum = query.getInt(1);
            }
            sum = sum % pageSize == 0 ? sum / pageSize : sum / pageSize + 1;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeConn();
        }
        return sum;
    }

    public List<News> getNewsPageList(int pageIndex, int pageSize) {
        db = new DButil();
        cu = new ChnlUtil();
        List<News> list = new ArrayList<>();
        String sql = "select * from news order by top desc limit ?,?";
        try {
            ResultSet query = db.query(sql, new Object[]{(pageIndex - 1) * pageSize, pageSize});
            while (query.next()) {
                News news = new News();
                news.setId(query.getLong("id"));
                news.setTitle(query.getString("title"));
                news.setChnlCode(query.getString("chnl_code"));
                news.setDetail(query.getString("detail"));
                news.setTop(query.getInt("top"));
                news.setOptor(query.getString("optor"));
                news.setCrtime(query.getTimestamp("crtime"));
                news.setUptime(query.getTimestamp("uptime"));
                news.setKeyword(query.getString("keyword"));
                String chnl = cu.findChnlName(query.getString("chnl_code"));
                news.setChnlName(chnl);
                list.add(news);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeConn();
        }
        return list;
    }

    public List<News> getNewsPageListByUser(List<NewsChnl> chnl,int pageIndex, int pageSize){
        db = new DButil();
        cu = new ChnlUtil();
        List<News> list = new ArrayList<>();
        Object[] obj = new Object[chnl.size()+2];
        StringBuilder sql = new StringBuilder("select * from news where chnl_code in ( ");
        try {
            for (int i = 0; i < chnl.size(); i++) {
                obj[i] = chnl.get(i).getCode();
                sql.append("?,");
            }
            sql.replace(sql.length()-1,sql.length(),")");
            obj[obj.length-2]=(pageIndex - 1) * pageSize;
            obj[obj.length-1]=pageSize;
            sql.append("order by top desc limit ?,?");
            ResultSet query = db.query(sql.toString(), obj);
            while (query.next()){
                News news = new News();
                String chnl_code = query.getString("chnl_code");
                news.setId(query.getLong("id"));
                news.setTitle(query.getString("title"));
                news.setChnlCode(chnl_code);
                news.setDetail(query.getString("detail"));
                news.setTop(query.getInt("top"));
                news.setOptor(query.getString("optor"));
                news.setCrtime(query.getTimestamp("crtime"));
                news.setUptime(query.getTimestamp("uptime"));
                news.setKeyword(query.getString("keyword"));
                String chnlName = cu.findChnlName(chnl_code);
                news.setChnlName(chnlName);
                list.add(news);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeConn();
        }
        return list;
    }
    public boolean findNewsByChnl(String newsCenter, String chnlName) {
        db = new DButil();
        cu = new ChnlUtil();
        try {
            String chnlCode = cu.findChnlName(chnlName);
            String sql = "select * from news where title=? and chnl_code=?";
            Object[] obj = {newsCenter, Integer.valueOf(chnlCode)};
            ResultSet query = db.query(sql, obj);
            if (query.next()) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeConn();
        }
        return false;
    }

    public List<News> selNewsByKeyword(String chnlName, String keyword, int pageIndex, int pageSize) {
        db = new DButil();
        cu = new ChnlUtil();
        List<News> list = new ArrayList<>();
        try {
            chnlName = chnlName.equals("=请选择=") ? "" : chnlName;
            String sql = null;
            Object[] obj = null;
            if (chnlName.isEmpty()) {
                sql = "select * from news where title like ? or keyword like ? order by top desc limit ?,?";
                obj =new Object[]  {"%" + keyword + "%", "%" + keyword + "%", (pageIndex - 1) * pageSize, pageSize};
            } else {
                String chnlCode = cu.findChnlName(chnlName);
                sql = "select * from news where(title like ? or keyword like ?)and chnl_code=? order by top desc limit ?,?";
                obj =new Object[] {"%" + keyword + "%", "%" + keyword + "%",Integer.parseInt(chnlCode), (pageIndex - 1) * pageSize, pageSize};
            }
            ResultSet query = db.query(sql, obj);
            while (query.next()){
                News news = new News();
                news.setId(query.getLong("id"));
                news.setTitle(query.getString("title"));
                news.setChnlCode(query.getString("chnl_code"));
                news.setDetail(query.getString("detail"));
                news.setTop(query.getInt("top"));
                news.setOptor(query.getString("optor"));
                news.setCrtime(query.getTimestamp("crtime"));
                news.setUptime(query.getTimestamp("uptime"));
                news.setKeyword(query.getString("keyword"));
                String chnl = cu.findChnlName(query.getString("chnl_code"));
                news.setChnlName(chnl);
                list.add(news);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeConn();
        }
        return list;
    }
    public List<News> selNewsByKeyword(List<NewsChnl> chnls,String chnlName, String keyword, int pageIndex, int pageSize){
        db = new DButil();
        cu = new ChnlUtil();

        List<News> list = new ArrayList<>();
        chnlName = chnlName.equals("=请选择=") ? "" : chnlName;
        try {
            String sql = null;
            Object[] obj = null;
            if (chnlName.isEmpty()) {
                obj = new Object[chnls.size()+4];
                obj[0] = "%" + keyword + "%";
                obj[1] = "%" + keyword + "%";
                sql =  "select * from news where (title like ? or keyword like ?) and chnl_code in (";
                StringBuffer sb = new StringBuffer(sql);
                for (int i = 0; i < chnls.size(); i++) {
                    obj[i+2] = chnls.get(i).getCode();
                    sb.append("?,");
                }
                sb.replace(sb.length()-1,sb.length(),")");
                sb.append("order by top desc limit ?,?");
                obj[obj.length-2] = (pageIndex - 1) * pageSize;
                obj[obj.length-1] = pageSize;
                sql = sb.toString();
            } else {
                String chnlCode = cu.findChnlName(chnlName);
                sql = "select * from news where(title like ? or keyword like ?)and chnl_code=? order by top desc limit ?,?";
                obj =new Object[] {"%" + keyword + "%", "%" + keyword + "%",Integer.parseInt(chnlCode), (pageIndex - 1) * pageSize, pageSize};
            }
            ResultSet query = db.query(sql, obj);
            while (query.next()){
                News news = new News();
                news.setId(query.getLong("id"));
                news.setTitle(query.getString("title"));
                news.setChnlCode(query.getString("chnl_code"));
                news.setDetail(query.getString("detail"));
                news.setTop(query.getInt("top"));
                news.setOptor(query.getString("optor"));
                news.setCrtime(query.getTimestamp("crtime"));
                news.setUptime(query.getTimestamp("uptime"));
                news.setKeyword(query.getString("keyword"));
                String chnl = cu.findChnlName(query.getString("chnl_code"));
                news.setChnlName(chnl);
                list.add(news);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeConn();
        }
        return list;
    }
    public Integer getSelSumPage(List<NewsChnl> chnls,String chnlName, String keyword,int pageSize){
        db = new DButil();
        cu = new ChnlUtil();
        Integer sumPage = null;
        try {
            chnlName = chnlName.equals("=请选择=") ? "" : chnlName;
            String sql = null;
            Object[] obj = null;
            if(chnlName.isEmpty()){
                obj = new Object[chnls.size()+2];
                obj[0] = "%" + keyword + "%";
                obj[1] = "%" + keyword + "%";
                sql = "select count(*) from news where (title like ? or keyword like ?) and chnl_code in (";
                StringBuffer sb = new StringBuffer(sql);
                for (int i = 0; i < chnls.size(); i++) {
                    obj[i+2] = chnls.get(i).getCode();
                    sb.append("?,");
                }
                sb.replace(sb.length()-1,sb.length(),")");
                sql = sb.toString();
            }else {
                String chnlCode = cu.findChnlName(chnlName);
                sql = "select count(*) from news where title like ? or keyword like ? and chnl_code=?";
                obj = new Object[]{"%" + keyword + "%", "%" + keyword + "%",Integer.parseInt(chnlCode)};
            }
            ResultSet query = db.query(sql, obj);
            if(query.next()){
                sumPage = query.getInt(1);
            }
            sumPage = sumPage % pageSize == 0 ? sumPage / pageSize : sumPage /pageSize + 1;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            db.closeConn();
        }
        return sumPage;
    }
    public Integer getSelSumPage(String chnlName, String keyword,int pageSize){
        db = new DButil();
        cu = new ChnlUtil();
        Integer sumPage = null;
        try {
            chnlName = chnlName.equals("=请选择=") ? "" : chnlName;
            String sql = null;
            Object[] obj = null;
            if(chnlName.isEmpty()){
                sql = "select count(*) from news where title like ? or keyword like ?";
                obj = new Object[]{"%" + keyword + "%", "%" + keyword + "%"};
            }else {
                String chnlCode = cu.findChnlName(chnlName);
                sql = "select count(*) from news where title like ? or keyword like ? and chnl_code=?";
                obj = new Object[]{"%" + keyword + "%", "%" + keyword + "%",Integer.parseInt(chnlCode)};
            }
            ResultSet query = db.query(sql, obj);
            if(query.next()){
                sumPage = query.getInt(1);
            }
            sumPage = sumPage % pageSize == 0 ? sumPage / pageSize : sumPage /pageSize + 1;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            db.closeConn();
        }
        return sumPage;
    }
    public boolean setNewsTop(String top,String id){
        db = new DButil();
        String sql = "update news set top=? where id=?";
        Object[] obj = {Integer.parseInt(top),Integer.parseInt(id)};
        try {
            db.update(sql,obj);
            db.submitUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                db.getConn().rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return false;
        } finally {
            db.closeConn();
        }
        return true;
    }
    public boolean delNews(String delNewsID){
        db = new DButil();
        try {
            if(!delNewsID.isEmpty()){
                String[] split = delNewsID.split(",");
                StringBuilder sql = new StringBuilder("delete from news where id in (");
                for (int i = 0; i < split.length; i++) {
                    sql.append("?,");
                }
                sql.replace(sql.length()-1,sql.length(),")");
                db.update(sql.toString(),split);
                db.submitUpdate();
            }
        } catch (Exception e) {
            e.printStackTrace();
            try {
                db.getConn().rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return false;
        } finally {
            db.closeConn();
        }
        return true;
    }
    public boolean updateNews(String newTitle,String keyword, String newsChnlName,int newsTop,String newsText,String newsId){
        db = new DButil();
        cu = new ChnlUtil();
        try {
            String chnlCode = cu.findChnlName(newsChnlName);
            Timestamp tt = new Timestamp(new Date().getTime());
            String sql = "update news set title=?,chnl_code=?,detail=?,keyword=?,top=?,uptime=? where id=?";
            Object[] obj = {newTitle,chnlCode,newsText,keyword,newsTop,tt,newsId};
            db.update(sql,obj);
            db.submitUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                db.getConn().rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return false;
        }finally {
            db.closeConn();
        }
        return true;
    }
}